#!/usr/bin/python
#coding=utf-8

from sqlalchemy import create_engine
from sqlalchemy.types import VARCHAR,INTEGER,DECIMAL
import pandas as pd
import datetime
from sqlalchemy.pool import NullPool
from common import db_utils as util
import sys

dtypedict = {'ACCOUNT': INTEGER,
            'COST_CENTER': VARCHAR,'UNIT_CODE': VARCHAR,'TEAM': VARCHAR,'SHIFT': VARCHAR,
            'WORK_DATE': VARCHAR,'MAT_NO': VARCHAR,'IN_MAT_NO': VARCHAR,'ST_NO': VARCHAR,
            'TOTALCOST_AMT_FACT': DECIMAL,'COST1_FACT': DECIMAL,'COST2_FACT': DECIMAL,
            'COST3_FACT': DECIMAL,'COST4_FACT': DECIMAL,'COST5_FACT': DECIMAL,
            'COST6_FACT': DECIMAL,'COST7_FACT': DECIMAL,'COST8_FACT': DECIMAL,
            'ACT_WT': DECIMAL,'ACT_IN_WT': DECIMAL,'THICK': VARCHAR,'THICK_COD': VARCHAR,
            'WIDTH': VARCHAR, 'WIDTH_COD': VARCHAR, 'DESIGN_ANNEAL_DIAGRAM_CODE': VARCHAR, 'TRIM_FLAG': VARCHAR,
            'IN_WIDTH': VARCHAR, 'IN_WIDTH_COD': VARCHAR, 'IN_THICK': VARCHAR, 'IN_THICK_COD': VARCHAR,
            'PLAN_NO_C': VARCHAR, 'PLAN_NO_COD': VARCHAR, 'TRIM_WIDTH_C': VARCHAR, 'TRIM_WIDTH_COD': VARCHAR,
            'IN_MAT_DIV_CODE': VARCHAR, 'IN_MAT_DIV': VARCHAR, 'LAS_NOTCH_FLAG': VARCHAR, 'QS_CODE': VARCHAR,
            'QS_NAME': VARCHAR, 'REC_CREATOR': VARCHAR, 'REC_CREATE_TIME': VARCHAR, 'REC_REVISOR': VARCHAR,'REC_REVISOR_TIME': VARCHAR
            }

def getConnectionDb2(host, port, dbname, user, password):
    # conn = pg.connect(host=host, port=port, dbname=dbname, user=user, password=password)
    engine = create_engine('ibm_db_sa://' + user + ':' + password + '@' + host + ':' + str(port) + '/' + dbname,
                           encoding="utf-8", poolclass=NullPool)
    return engine.connect()

def save_as_table(df,table_name,conn,schema_name=None,dtypedict=None):
    return pd.io.sql.to_sql(df, table_name, conn,schema_name,if_exists='append',index=False,chunksize=10000,dtype=dtypedict)

def main(unit_code,start_time,end_time):
    # 先删除这段时间的数据
    del_sql = """delete from BGTARSSI00.T_DWD_FACT_CB00SI_CD0201 WHERE COST_CENTER='MIDE' and WORK_DATE BETWEEN '""" + str(start_time) + """' AND '""" + str(end_time) + """'"""
    conn_rds.execute(del_sql)

    sql = """SELECT 
                ACCOUNT,COST_CENTER,UNIT_CODE,TEAM,SHIFT,WORK_DATE,MAT_NO,IN_MAT_NO,ST_NO,TOTALCOST_AMT_FACT,
                COST1_FACT,COST2_FACT,COST3_FACT,COST4_FACT,COST5_FACT,COST6_FACT,COST7_FACT,COST8_FACT,
                ACT_WT,ACT_IN_WT,THICK,THICK_COD,WIDTH,WIDTH_COD,DESIGN_ANNEAL_DIAGRAM_CODE,TRIM_FLAG,
                IN_WIDTH,IN_WIDTH_COD,IN_THICK,IN_THICK_COD,PLAN_NO_C,PLAN_NO_COD,
                TRIM_WIDTH_C,TRIM_WIDTH_COD,IN_MAT_DIV_CODE,IN_MAT_DIV,LAS_NOTCH_FLAG,QS_CODE,QS_NAME,
                'BGRAGGCB' AS REC_CREATOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_CREATE_TIME,
                '' AS REC_REVISOR,
                TO_CHAR(CURRENT TIMESTAMP,'YYYYMMDDHH24MISS') AS REC_REVISOR_TIME
                FROM BGRAGGCB.SU_AJBG_CD0201 WHERE UNIT_CODE = '""" + unit_code + """' AND WORK_DATE BETWEEN '""" + str(start_time) + """' AND '""" + str(end_time) + """'
            """
    print(sql)
    df = util.query(conn_db2, sql)
    util.insert(df, conn_rds, "T_DWD_FACT_CB00SI_CD0201", "BGTARSSI00", dtypedict)

if __name__ == '__main__':
    '''先读配置表
      再获取RDS连接
    '''
    now_time = datetime.datetime.now()
    start_time = (now_time - datetime.timedelta(minutes=360)).strftime("%Y%m%tmp_dict%H%M%S")
    end_time = now_time.strftime("%Y%m%tmp_dict%H%M%S")
    # start_time = '20210321200000'
    # end_time = '20210322200000'
    global conn_db2,conn_rds
    conn_db2 = util.getConnectionDb2('10.70.16.170', '50170 ', 'dbprode7', 'dbprode7', 'dbprode7')
    conn_rds = util.getConnectionDb2('10.70.48.121', '50000 ', 'RG0PROD', 'bgtaroq000', 'bgtaroq000bgta')
    main("Q518",start_time,end_time)
    conn_rds.close()
    conn_db2.close()
    sys.exit()